﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace studentmanagementdemo
{
    public partial class frmsinhvien : Form
    {
        private SqlConnection con;
        private DataTable dt = new DataTable("tblStudent");
        private DataTable dtClass = new DataTable("tblClass");
        private SqlDataAdapter da = new SqlDataAdapter();
        private Boolean kt;

        private void connect()
        {
            //String cn = "Data Source=127.0.0.1;Initial Catalog=iPMAC;Persist Security Info=True;User ID=sa;Password=sa";
            String cn = @"Data Source=(localdb)\linh123;Initial Catalog=iPMAC;Integrated Security=True";
            try
            {
                con = new SqlConnection(cn);
                con.Open(); //Moi ket noi                
            }
            catch (Exception ex)
            {
                MessageBox.Show("Không thể kết nối tới cơ sở dữ liệu !", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void disconnect()
        {
            con.Close(); //Dong ket noi
            con.Dispose(); //Giai phong tai nguyen
            con = null; //Huy doi tuong
        }

        public frmsinhvien()
        {
            InitializeComponent();
        }

        private void frmsinhvien_Load(object sender, EventArgs e)
        {
            connect();
            getdata();
            binding();
        }

        private void getdata()
        {
            SqlCommand command = new SqlCommand(); //Khai bao 1 command
            command.Connection = con; //Ket noi
            command.CommandType = CommandType.Text; //Khai bao kieu command
            command.CommandText = @"Select 
                                        tblStudent.fldID as N'MaSV', 
                                        fldFirstName as N'HoSV',
                                        fldLastName as N'TenSV', 
                                        fldAge as N'Tuoi', 
                                        (Case fldSex when 'True' then N'Nam' 
                                            when 'False' then N'Nữ' 
                                            else N'Chưa xác định' end) as N'GioiTinh',  
                                        fldCreatedDate as N'CreatedDate', 
                                        IsNULL(fldClassName, N'Chưa học') as N'TenLop',
                                        fldClassID as N'ClassID' 
                                  from tblStudent 
                                        left outer join tblClass 
                                        on tblStudent.fldClassID = tblClass.fldID"; //Cau truy van SQL
            da.SelectCommand = command; //Gan command cho da            
            da.Fill(dt); //Nap du lieu vao table
            grvsinhvien.DataSource = dt; //Load du lieu len DataGridView                      

            //Dung store:
            //command.CommandType = CommandType.StoredProcedure;
            //command.CommandText = "toanbothongtinsv";
            //SqlDataReader daReader = command.ExecuteReader();            
            //dt.Load(daReader);
            //dtdata.DataSource = dt;

            command.CommandText = "Select * From tblClass";
            da.SelectCommand = command;
            da.Fill(dtClass);
            cbolop.DataSource = dtClass;
            cbolop.DisplayMember = "fldClassName";
            cbolop.ValueMember = "fldID";


        }

        private void binding()
        {
            txtmasv.DataBindings.Clear();
            txtmasv.DataBindings.Add("Text", grvsinhvien.DataSource, "MaSV");
            txthosv.DataBindings.Clear();
            txthosv.DataBindings.Add("Text", grvsinhvien.DataSource, "HoSV");
            txttensv.DataBindings.Clear();
            txttensv.DataBindings.Add("Text", grvsinhvien.DataSource, "TenSV");
            txttuoi.DataBindings.Clear();
            txttuoi.DataBindings.Add("Text", grvsinhvien.DataSource, "Tuoi");
            cbogioitinh.DataBindings.Clear();
            cbogioitinh.DataBindings.Add("Text", grvsinhvien.DataSource, "GioiTinh");
            dtngay.DataBindings.Clear();
            dtngay.DataBindings.Add("Text", grvsinhvien.DataSource, "CreatedDate");
            cbolop.DataBindings.Clear();
            cbolop.DataBindings.Add("Text", grvsinhvien.DataSource, "TenLop");
        }

        private void cmdthemmoi_Click(object sender, EventArgs e)
        {
            frmsinhvienthem _frmsinhvienthem = new frmsinhvienthem();
            _frmsinhvienthem.Show();
            Hide();
        }

        private void btnthoat_Click(object sender, EventArgs e)
        {
            Close();
            Dispose();
            disconnect();
        }

        private bool kiemtra()
        {
            kt = true;
            if (txthosv.Text == "")
            {
                MessageBox.Show("Bạn chưa nhập họ của SV !", "THÔNG BÁO", MessageBoxButtons.OK);
                kt = false;
            }
            else if (dtngay.Value == null)
            {
                MessageBox.Show("Bạn chưa ngày tháng !", "THÔNG BÁO", MessageBoxButtons.OK);
                kt = false;
            }
            return kt;
        }

        private void btnsua_Click(object sender, EventArgs e)
        {
            if (txtmasv.Text.Length > 0)
            {
                kiemtra();
                if (kt == true)
                {
                    DataRow row = dt.Select("MaSV = " + Convert.ToInt32(txtmasv.Text))[0];
                    row.BeginEdit();
                    row["HoSV"] = txthosv.Text;
                    row["TenSV"] = txttensv.Text;
                    row["Tuoi"] = txttuoi.Text;
                    if (cbogioitinh.Text == "Nam")
                        row["GioiTinh"] = 1;
                    else if (cbogioitinh.Text == "Nữ")
                        row["GioiTinh"] = 0;
                    row["CreatedDate"] = dtngay.Value;
                    row["ClassID"] = cbolop.SelectedValue;
                    row.EndEdit();
                    SqlCommand commandUpdate = new SqlCommand();
                    commandUpdate.Connection = con;
                    commandUpdate.CommandType = CommandType.Text;
                    commandUpdate.CommandText = @"Update tblStudent Set fldFirstName = @fldFirstName, 
                                                                        fldLastName=@fldLastName, 
                                                                        fldAge=@fldAge, fldSex=@fldSex, 
                                                                        fldCreatedDate=@fldCreatedDate, 
                                                                        fldClassID=@fldClassID
                                                                        Where fldID = @fldID";
                    commandUpdate.Parameters.Add("@fldID", SqlDbType.Int, 50, "MaSV");
                    commandUpdate.Parameters.Add("@fldFirstName", SqlDbType.NVarChar, 50, "HoSV");
                    commandUpdate.Parameters.Add("@fldLastName", SqlDbType.NVarChar, 50, "TenSV");
                    commandUpdate.Parameters.Add("@fldAge", SqlDbType.Int, 50, "Tuoi");
                    commandUpdate.Parameters.Add("@fldSex", SqlDbType.Int, 50, "GioiTinh");
                    commandUpdate.Parameters.Add("@fldCreatedDate", SqlDbType.DateTime, 50, "CreatedDate");
                    commandUpdate.Parameters.Add("@fldClassID", SqlDbType.Int, 50, "ClassID");
                    da.UpdateCommand = commandUpdate;
                    da.Update(dt);
                    MessageBox.Show("Bạn đã sửa thành công !", "THÔNG BÁO", MessageBoxButtons.OK);
                }
            }
        }

        private void btnxoa_Click(object sender, EventArgs e)
        {
            if (txtmasv.Text.Length > 0)
            {
                DataRow row = dt.Select("MaSV = " + Convert.ToInt32(txtmasv.Text))[0];
                row.BeginEdit();
                row.Delete();
                row.EndEdit();
                SqlCommand commandDelete = new SqlCommand();
                commandDelete.Connection = con;
                commandDelete.CommandType = CommandType.Text;
                commandDelete.CommandText = "Delete From tblStudent where fldID = @fldID";
                commandDelete.Parameters.Add("@fldID", SqlDbType.Int, 50, "MaSV");
                da.DeleteCommand = commandDelete;
                da.Update(dt);
                MessageBox.Show("Bạn đã xóa thành công !", "THÔNG BÁO", MessageBoxButtons.OK);
            }
        }

        private void btntim_Click(object sender, EventArgs e)
        {
            dt.Clear();
            SqlCommand command = new SqlCommand(); //Khai bao 1 command
            command.Connection = con; //Ket noi            
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "store_timkiem";
            command.Parameters.Add("@TenSV", SqlDbType.NVarChar, 50).Value = txttensvtim.Text;
            if (cbogioitinhtim.Text == "Nam")
            {
                command.Parameters.Add("@GioiTinh", SqlDbType.Int, 50).Value = 1;
            }
            else if (cbogioitinhtim.Text == "Nữ")
            {
                command.Parameters.Add("@GioiTinh", SqlDbType.Int, 50).Value = 0;
            }
            else
                command.Parameters.Add("@GioiTinh", SqlDbType.Int, 50).Value = 3;
            SqlDataReader daReader = command.ExecuteReader();
            dt.Load(daReader);
            if (dt.Rows.Count > 0)
            {
                grvsinhvien.DataSource = dt; //Load du lieu len DataGridView  
            }
            else
            {
                MessageBox.Show("Không tìm thấy dữ liệu phù hợp !", "THÔNG BÁO", MessageBoxButtons.OK);
            }
        }

        private void btnreset_Click(object sender, EventArgs e)
        {
            txttensvtim.Text = "";
            cbogioitinhtim.Text = "";
            dt.Clear();
            getdata();
        }
    }
}
